package com.rivues.task.resource;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.rivu.tools.ParamTools;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.rivues.core.RivuDataContext;
import com.rivues.module.platform.web.model.JobDetail;
import com.rivues.module.platform.web.model.TableProperties;
import com.rivues.module.platform.web.model.TableTask;
import com.rivues.util.RivuTools;
import com.rivues.util.data.DatabaseMetaDataHandler;
import com.rivues.util.tools.TabelSql;

public class DataExchangeResource extends Resource {
	private final Logger log = LoggerFactory.getLogger(DataExchangeResource.class); 
	private TabelSql tableSQL = null ;
	private JobDetail job ;
	private Properties properties = null ;
	private TableTask source ;
	private TableTask target ;
	/**
	 * 构造器
	 * @param job
	 */
	public DataExchangeResource(JobDetail job){
		this.job = job ;
		initTableTask(this.job.getOrgi());
	}
	
	/**
	 * 初始化 结算表数据信息
	 */
	private void initTableTask(String orgi){
		List<TableTask> tableTaskList = RivuDataContext.getService().findAllByCriteria(DetachedCriteria.forClass(TableTask.class).add(Restrictions.and(Restrictions.eq("orgi", orgi) , Restrictions.eq("id", this.job.getSource())))) ;
		if(tableTaskList.size()>0){
			this.source = tableTaskList.get(0) ;
		}
		tableTaskList = RivuDataContext.getService().findAllByCriteria(DetachedCriteria.forClass(TableTask.class).add(Restrictions.and(Restrictions.eq("orgi", orgi) , Restrictions.eq("id", this.job.getTargettask())))) ;
		if(tableTaskList.size()>0){
			this.target = tableTaskList.get(0) ;
		}
	}
	
	private void resetConnection(final TableTask task ) throws Exception{
		Class.forName(task.getDatabase().getDriverclazz()) ;
		properties = ParamTools.getProperties(task.getDatabase().getConnectparam());
		task.setConn(DriverManager.getConnection(task.getDatabase().getDatabaseurl(), task.getDatabase().getAccount(),RivuTools.decryption(task.getDatabase().getPassword()))) ;
	}
	
	/**
	 * 获取数据
	 * @param task
	 * @return
	 * @throws Exception
	 */
	private void getResultSet(TableTask task) throws Exception{
		tableSQL = DatabaseMetaDataHandler.getSQL(properties,task , this.job.getStartindex() , 0) ;
		System.out.println("SQL:"+tableSQL.getSql()) ;
		task.setStatment(task.getConn().prepareStatement(tableSQL.getSql())) ;
		
		if(tableSQL.isParame()){ //never be run
			if( job.getStartindex()>0){
				task.getStatment().setInt(1, (int)job.getStartindex()) ;
				task.getStatment().setInt(2, 50000) ;
				if(tableSQL.getUpdatetime()!=null){
					task.getStatment().setDate(3, new java.sql.Date(task.getUpdatetime().getTime())) ;
				}
				
			}else{
				task.getStatment().setInt(1, 50000) ;
				if(tableSQL.getUpdatetime()!=null){
					task.getStatment().setDate(2, new java.sql.Date(task.getUpdatetime().getTime())) ;
				}
			}
		}else{
			if(tableSQL.getModitp()!=null){
				task.getStatment().setTimestamp(1, new java.sql.Timestamp((task.getLastupdate()!=null?task.getLastupdate():new Date(0L)).getTime())) ;
			}
			if(task.getUserid()!=null && task.getUserid().equals("1")){
				task.getStatment().setInt(1, (int)job.getStartindex()) ;
			}
		}
		task.setResultSet(task.getStatment().executeQuery());//DatabaseMetaDataHandler.getTableData(conn , statment , properties, task) ;
		if(properties.getProperty("num")==null && properties.getProperty("dialect")==null){
			if(tableSQL.getModitp()==null && job.getStartindex()>0){ // if change update by time to startindex , may be lost same data 
				for(int i=0 ; i<job.getStartindex() ; i++){
					task.getResultSet().next() ;
				}
			}
		}
	}
	
	@Override
	public void begin() throws Exception {
		if(this.source!=null && this.target!=null){
			resetConnection(source);
			getResultSet(source);
			resetConnection(target);
		}else{
			throw new Exception("Setting Error");
		}
	}

	@Override
	public void end(boolean clear) throws Exception {
		if(this.source!=null){
			this.source.close() ;
		}
		if(this.target!=null){
			this.target.close();
		}
	}

	@Override
	public JobDetail getJob() {
		return job;
	}

	@Override
	public void process(OutputTextFormat meta, JobDetail job) {
		if(target!=null && target.getConn()!=null){
			Statement statement = null ;
			for(SQLDataValue sqlData : meta.getSql()){
				try{
					if(sqlData.getParanum()>0){
						statement = target.getConn().prepareStatement(sqlData.getSql()) ;
						for(int i=1 ; i <= sqlData.getMetadata().size() ; i++){
							((PreparedStatement)statement).setObject(i, sqlData.getMetadata().get(i-1));
						}
						((PreparedStatement)statement).executeUpdate() ;
					}else{
						statement = target.getConn().createStatement();
						statement.execute(sqlData.getSql());
					}
				}catch(Exception ex){
					ex.printStackTrace();
				}finally{
					if(statement!=null){
						try {
							statement.close() ;
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
	}

	@Override
	public OutputTextFormat next() throws Exception {
		if(this.job==null)
			return null ;
		return createOutputTextFormat(target);
	}

	/**
	 * 获得数据
	 * @param task
	 * @return
	 * @throws SQLException
	 */
	private synchronized OutputTextFormat createOutputTextFormat(TableTask task) throws SQLException{
		OutputTextFormat meta = null ;
		if(source.getResultSet()!=null && source.getResultSet().next()){
			meta = new OutputTextFormat(job)  ;
			meta.setJob(job) ;
			meta.setTitle(task.getDatabase().getTaskname()) ;
			meta.setParent(task.getTaskname()) ;
			boolean isAutoID = false ;
			StringBuffer strb = new StringBuffer() ,field = new StringBuffer()  ,wherecon = new StringBuffer()  ,  pkFieldValue = new StringBuffer();
			SQLDataValue dataValue = new SQLDataValue();
			for(TableProperties tp : task.getTableproperty()){
				try{
					if(field.length()>0){
						field.append(",") ;
					}
					if(wherecon.length()>0){
						wherecon.append(",") ;
					}
					field.append(tp.getFieldname()) ;
					
					wherecon.append("?");
					if("R3_DATA_ID".equals(tp.getFieldname())){
						isAutoID = true ;
						dataValue.getMetadata().add("R3_DATA_ID") ;
					}else{
						TableProperties sourcTableProperty = getSourceTablePropertie(tp);
						dataValue.setParanum(dataValue.getParanum()+1) ;
						Object obj = source.getResultSet().getObject(sourcTableProperty.getFieldname()) ;
						String value = null ;
						if(obj!=null){
							value = obj.toString() ;
						}
						if((tp.getPk()!=null && tp.getPk()==true) || (sourcTableProperty.getPk()!=null && sourcTableProperty.getPk()==true)){
							if(pkFieldValue.length()>0){
								pkFieldValue.append("_") ;
							}
							pkFieldValue.append(value) ;
						}
						dataValue.getMetadata().add(value) ;
					}
				}catch(Exception ex){
					ex.printStackTrace();
				}
			}
			strb.append("INSERT INTO ").append(this.target.getTablename()).append("(");
			strb.append(field.toString()).append(") VALUES(");
			strb.append(wherecon.toString()) ;
			strb.append(")");
			dataValue.setSql(strb.toString()) ;
			if(isAutoID && pkFieldValue.length()>0){
				String id = RivuTools.md5(pkFieldValue.toString()) ;
				for(int i= 0 ; i<dataValue.getMetadata().size() ; i++){
					if("R3_DATA_ID".equals(dataValue.getMetadata().get(i))){
						dataValue.getMetadata().set(i, id) ;
					}
				}
				
				strb = new StringBuffer();
				strb.append("DELETE FROM ").append(this.target.getTablename()).append(" WHERE R3_DATA_ID = ?");
				SQLDataValue deleteValue = new SQLDataValue();
				deleteValue.setParanum(1) ;
				deleteValue.setSql(strb.toString());
				deleteValue.getMetadata().add(id) ;
				meta.getSql().add(deleteValue) ;
			}
			
			meta.getSql().add(dataValue) ;
		}
		return meta;
	}
	/**
	 * 
	 * @param tableProperty
	 * @return
	 */
	private TableProperties getSourceTablePropertie(TableProperties tableProperty){
		TableProperties retTableProperty = tableProperty;
		if(this.source!=null && this.source.getTableproperty()!=null){
			for(TableProperties tp : this.source.getTableproperty()){
				if(tableProperty.getFieldname().equalsIgnoreCase(tp.getFieldname())){
					retTableProperty = tp ;
					break ;
				}
			}
		}
		return retTableProperty;
	}
	
	@Override
	public boolean isAvailable() {
		return true;
	}

	@Override
	public OutputTextFormat getText(OutputTextFormat object) throws Exception {
		return object;
	}

	@Override
	public void rmResource() {
	}

	@Override
	public void updateTask() {
	}

}
